﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using Excel = Microsoft.Office.Interop.Excel;

namespace LOGIN
{
    public partial class cancel : Form
    {
        private DataTable ordersTable;
        private DataView OrdersViewByName;
        private DataView OrdersViewBysupplyer;
        public cancel()
        {
            InitializeComponent();
           
            //this.StartPosition = FormStartPosition.CenterScreen;                               
            this.dgv_orders.AllowUserToAddRows = false;                          
            //this.dgv_orders.RowHeadersVisible = false;                                                     
            this.dgv_orders.BackgroundColor = Color.White;                                  
            this.dgv_orders.AutoSizeColumnsMode =
                DataGridViewAutoSizeColumnsMode.AllCells;                                      
        }

        private void OrdersTable_Load(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString =
                "Server=(local);Database=MyHospital;Integrated Security=sspi";
            SqlCommand sqlCommand = new SqlCommand();
            SqlCommand sqlCommand1 = new SqlCommand();
         
            sqlCommand.Connection = sqlConnection;
            sqlCommand1.Connection = sqlConnection;
            
            sqlCommand.CommandText = "select OrderID,SupplyID,OrderKinds,OrderDate,GetDate,ProducePla,State,c.DrugName,cs.IndPrice,Num,TolPrice,cs.Guige,cs.cancel FROM orders cs JOIN dbo.medicineData c ON cs.MedicineID=c.MedicineID;";
            sqlCommand1.CommandText = "select SupplyID,Supplyname from supplyer;";
            
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            
            SqlDataAdapter sqlDataAdapter1 = new SqlDataAdapter();                                        
            sqlDataAdapter1.SelectCommand = sqlCommand1; 
                                                
            DataTable supplyerTable = new DataTable();
            sqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;                           
            this.ordersTable = new DataTable();
            
            sqlConnection.Open();
            sqlDataAdapter.Fill(this.ordersTable);
            sqlDataAdapter1.Fill(supplyerTable);
           
            sqlConnection.Close();
            this.ordersTable.TableName = "tablename";
            this.OrdersViewByName = new DataView();                                                        
            this.OrdersViewByName.Table = this.ordersTable;                                                
            this.OrdersViewByName.Sort = "DrugName ASC";

            this.OrdersViewBysupplyer = new DataView();                                                        
            this.OrdersViewBysupplyer.Table = this.ordersTable;                                               
            this.OrdersViewBysupplyer.Sort = "ProducePla ASC";               
  
            this.dgv_orders.DataSource = ordersTable;
            dgv_orders.Columns["OrderID"].ReadOnly = true;
            dgv_orders.Columns["OrderKinds"].ReadOnly = true;
            dgv_orders.Columns["State"].ReadOnly = true;
            dgv_orders.Columns["DrugName"].ReadOnly = true;
            dgv_orders.Columns["OrderID"].HeaderText = "订单号";
            dgv_orders.Columns["SupplyID"].Visible=false;
            dgv_orders.Columns["OrderKinds"].HeaderText = "订单";
            dgv_orders.Columns["OrderDate"].HeaderText = "订单日期";
            dgv_orders.Columns["GetDate"].HeaderText = "到货日期";
            dgv_orders.Columns["ProducePla"].HeaderText = "生产地";
            dgv_orders.Columns["State"].HeaderText = "是否入库";
            dgv_orders.Columns["DrugName"].HeaderText = "药品";
            dgv_orders.Columns["DrugName"].DisplayIndex=3;
            dgv_orders.Columns["IndPrice"].HeaderText = "单价";
            dgv_orders.Columns["Num"].HeaderText = "数量";
            dgv_orders.Columns["TolPrice"].HeaderText = "总价";
            dgv_orders.Columns["Guige"].HeaderText = "规格";
            dgv_orders.Columns["cancel"].HeaderText = "取消订单";
            //this.dgv_orders.Columns[this.dgv_orders.Columns.Count - 1].AutoSizeMode =
            //    DataGridViewAutoSizeColumnMode.Fill;

            DataGridViewComboBoxColumn supplyerColumn = new DataGridViewComboBoxColumn();                   
            supplyerColumn.Name = "Supplyname";                                                                  
            supplyerColumn.HeaderText = "供应商";                                                           
            supplyerColumn.DataSource = supplyerTable;
            supplyerColumn.DisplayMember = "Supplyname";                                                             
            supplyerColumn.ValueMember = "SupplyID";                                                             
            supplyerColumn.DataPropertyName = "SupplyID";                                                 
            supplyerColumn.DisplayIndex = 1;                                                               
            //supplyerColumn.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;                           
            this.dgv_orders.Columns.Add(supplyerColumn);

            DataGridViewColumn medicineColumn = new DataGridViewColumn();                      
            medicineColumn.Name = "medicine";                                                                   
            medicineColumn.HeaderText = "药品名";

            for (int m = 0; m < Convert.ToInt32(dgv_orders.RowCount.ToString()); m++)
            {
                if (Convert.ToInt32(dgv_orders.Rows[m].Cells["cancel"].Value)==1)
                {
                    dgv_orders.Rows[m].DefaultCellStyle.ForeColor = Color.Red;
                }
            }

            
            
        }

        private void update_Click(object sender, EventArgs e)
        {
            SqlConnection sqlConnection = new SqlConnection();                                           
            sqlConnection.ConnectionString =
                ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;                             
            SqlCommand updateCommand = new SqlCommand();                                                  
            updateCommand.Connection = sqlConnection;                                                         
            updateCommand.CommandText =                                                                      
                "UPDATE orders"
                + " SET SupplyID=@SupplyID,OrderDate=@OrderDate,GetDate=@GetDate,ProducePla=@ProducePla,IndPrice=@IndPrice,Num=@Num,TolPrice=@TolPrice,Guige=@Guige,state=@state,cancel=@cancel"
                + " WHERE OrderID=@OrderID;";
            updateCommand.Parameters.Add("@OrderID", SqlDbType.Char, 10, "OrderID");
            updateCommand.Parameters.Add("@SupplyID", SqlDbType.Char, 10, "SupplyID");                      
            updateCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime, 0, "OrderDate");
            updateCommand.Parameters.Add("@GetDate", SqlDbType.DateTime, 0, "GetDate");
            updateCommand.Parameters.Add("@ProducePla", SqlDbType.VarChar, 0, "ProducePla");
            updateCommand.Parameters.Add("@IndPrice", SqlDbType.Char, 0, "IndPrice");
            updateCommand.Parameters.Add("@Num", SqlDbType.Int, 0, "Num");
            updateCommand.Parameters.Add("@TolPrice", SqlDbType.Int, 10, "TolPrice");
            updateCommand.Parameters.Add("@Guige", SqlDbType.VarChar, 10, "Guige");                        
            updateCommand.Parameters.Add("@state", SqlDbType.Int, 0, "state");
            updateCommand.Parameters.Add("@cancel", SqlDbType.Int, 0, "cancel");
           

            SqlCommand deleteCommand = new SqlCommand();                                                
            deleteCommand.Connection = sqlConnection;                                                      
            deleteCommand.CommandText =                                                                  
                "DELETE orders"
                + " WHERE OrderID=@OrderID;";
            deleteCommand.Parameters.Add("@OrderID", SqlDbType.Char, 10, "OrderID");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();                                         
            //sqlDataAdapter.InsertCommand = insertCommand;                                                   
            sqlDataAdapter.UpdateCommand = updateCommand;                                               
            sqlDataAdapter.DeleteCommand = deleteCommand;                                                   
            DataTable studentTable1 = (DataTable)this.dgv_orders.DataSource;                                
            sqlConnection.Open();                                                                           
            int rowAffected = sqlDataAdapter.Update(studentTable1);                                         
            sqlConnection.Close();                                                                          
            MessageBox.Show("更新" + rowAffected.ToString() + "行。");     
        }

        private void input_Click(object sender, EventArgs e)
        {
            Orders f = new Orders();
            f.Show();
        }

        private void search_Click(object sender, EventArgs e)
        {
            if (this.txb_search.Text == "")
            {
                MessageBox.Show("请输入搜索内容");
            }
            else if (this.comboBox1.SelectedIndex == 0 && this.txb_search.Text != "") 
            {
                DataRow searchResultRow = this.ordersTable.Rows.Find(this.txb_search.Text.Trim());            
                DataTable searchResultTable = this.ordersTable.Clone();                                      
                searchResultTable.ImportRow(searchResultRow);                                              
                this.dgv_orders.DataSource = searchResultTable;
            }
            else if (this.comboBox1.SelectedIndex == 1 && this.txb_search.Text != "")
            {
                DataRowView[] searchResultRowViews = 
                this.OrdersViewByName.FindRows(this.txb_search.Text.Trim());                      
            DataTable searchResultTable = this.ordersTable.Clone();                                     
            foreach (DataRowView dataRowView1 in searchResultRowViews)                                 
            {
                searchResultTable.ImportRow(dataRowView1.Row);                                          
            }
            this.dgv_orders.DataSource = searchResultTable; 
            }
            else if (this.comboBox1.SelectedIndex == 2 && this.txb_search.Text != "")
            {
                DataRowView[] searchResultRowViews =
                this.OrdersViewBysupplyer.FindRows(this.txb_search.Text.Trim());            
                DataTable searchResultTable = this.ordersTable.Clone();                                       
                foreach (DataRowView dataRowView1 in searchResultRowViews)                                     
                {
                    searchResultTable.ImportRow(dataRowView1.Row);                                     
                }
                this.dgv_orders.DataSource = searchResultTable; 
            }
            
           
        }

        private void print_Click(object sender, EventArgs e)
        {
            string fileName = "";

            string saveFileName = "";

            SaveFileDialog saveDialog = new SaveFileDialog();

            saveDialog.DefaultExt = "xls";

            saveDialog.Filter = "Excel文件|*.xls";

            saveDialog.FileName = fileName;

            saveDialog.ShowDialog();

            saveFileName = saveDialog.FileName;

            if (saveFileName.IndexOf(":") < 0) return; //被点了取消

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

            if (xlApp == null)
            {

                MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");

                return;

            }

            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;

            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);

            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1 

             

            for (int i = 0; i < dgv_orders.ColumnCount; i++)

            { worksheet.Cells[1, i + 1] = dgv_orders.Columns[i].HeaderText; }

            

            for (int r = 0; r < dgv_orders.Rows.Count; r++)
            {
                for (int i = 0; i < dgv_orders.ColumnCount; i++)
                {

                    worksheet.Cells[r + 2, i + 1] = dgv_orders.Rows[r].Cells[i].Value;

                }

                System.Windows.Forms.Application.DoEvents();

            }

            worksheet.Columns.EntireColumn.AutoFit();

            MessageBox.Show(fileName + "信息导出成功", "提示", MessageBoxButtons.OK);

            if (saveFileName != "")
            {

                try
                {
                    workbook.Saved = true;

                    workbook.SaveCopyAs(saveFileName);                

                }

                catch (Exception ex)
                {                    

                    MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);

                }

            }

            xlApp.Quit();

            GC.Collect();
        }

        

        
    }
}
